import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import model.Employee;
import model.db.MyDBAccess;
/**
 * Servlet implementation class TestServlet
 */
@WebServlet("/TestServlet")
public class TestServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		String req_employee_no = request.getParameter("employee_no");
		String req_sex = request.getParameter("sex");
		String req_join_date_from = request.getParameter("join_date_from");
		String req_join_date_to = request.getParameter("join_date_to");
		String req_section = request.getParameter("section");
		String req_action_id = request.getParameter("action_id");
		String sql = "select employee_no, first_name, last_name, sex, join_date, section, mail from employee_info ";
		int i =0;
		int record_count = 0;
		if ((req_employee_no == null || req_employee_no.length() < 1)
			&& (req_sex == null || req_sex.compareTo("null") == 0)
			&& (req_join_date_from == null || req_join_date_from == "")
			&& (req_join_date_to == null || req_join_date_to == "")
			&& (req_section == null || req_section.compareTo("null") == 0)) {
			if("1".equals(req_action_id)){
				sql += "ORDER BY employee_no ASC;";
			}
			if("2".equals(req_action_id)){
				sql += "ORDER BY employee_no DESC;";
			}
			if("3".equals(req_action_id)){
				sql += "ORDER BY join_date ASC;";
			}
			if("4".equals(req_action_id)){
				sql += "ORDER BY join_date DESC;";
			}
		}else{
			sql += "WHERE ";
			if (req_employee_no != null && req_employee_no.length() > 0) {
				sql += "employee_no like '%" + req_employee_no + "%' ";
				i++;
			}
			if (req_sex != null && req_sex.compareTo("null") != 0 ) {
				if(i > 0){
					sql += "and sex ='" + req_sex + "' ";
				}else{
					sql += "sex ='" + req_sex + "' ";
					i++;
				}
			}
			if (req_join_date_from != null && req_join_date_from.length() > 0) {
				if(i > 0){
					sql += "and join_date >= " + req_join_date_from + " ";
				}else{
					sql += "join_date >= " + req_join_date_from + " ";
					i++;
				}
			}
			if (req_join_date_to != null && req_join_date_to.length() > 0) {
				if(i > 0){
					sql += "and join_date <= " + req_join_date_to + " ";
				}else{
					sql += "join_date <= " + req_join_date_to + " ";
					i++;
				}
			}
			if (req_section != null && req_section.compareTo("null") != 0) {
				if(i > 0){
					sql += "and section ='" + req_section + "' ";
				}else{
					sql += "section ='" + req_section + "' ";
				}
			}
			if("1".equals(req_action_id)){
				sql += "ORDER BY employee_no ASC;";
			}
			if("2".equals(req_action_id)){
				sql += "ORDER BY employee_no DESC;";
			}
			if("3".equals(req_action_id)){
				sql += "ORDER BY join_date ASC;";
			}
			if("4".equals(req_action_id)){
				sql += "ORDER BY join_date DESC;";
			}
		}
		//内容: データベースにアクセスする
		//MyDBAccess のインスタンスを生成する
		MyDBAccess db = new MyDBAccess();
		//昇順・降順用
		String sort_employee_no = request.getParameter("employee_no");
		String join_date_from = request.getParameter("join_date_from");
		String join_date_to = request.getParameter("join_date_to");
		if(sort_employee_no == null){
			sort_employee_no = "";
		}
		if(join_date_from == null){
			join_date_from = "";
		}
		if(join_date_to == null){
			join_date_to = "";
		}
		//データベースへのアクセス
		try {
			db.open();
		} catch (Exception e2) {
			// TODO 自動生成された catch ブロック
			e2.printStackTrace();
		}
		ResultSet rs = null;
		try {
			rs = db.getResultSet(sql);
		} catch (Exception e2) {
			// TODO 自動生成された catch ブロック
			e2.printStackTrace();
		}
		ArrayList<Employee> employees = new ArrayList<Employee>();
		// 取得された各結果に対しての処理
		try {
			while(rs.next()) {
			    String sex = rs.getString("sex"); // 性別を取得
			    String res_sex = "";
			    if(sex.equals("1")){
			    	res_sex = "男性";
			    }
			    if(sex.equals("2")){
			    	res_sex = "女性";
			    }
			    Employee employee = new Employee();
			    employee.employee_no = rs.getString("employee_no");
			    employee.first_name = rs.getString("first_name");
			    employee.last_name = rs.getString("last_name");
			    employee.sex = res_sex;
			    employee.join_date = rs.getString("join_date");
			    employee.section = rs.getString("section");
			    employee.mail = rs.getString("mail");
			    employees.add(employee);
			    record_count++;
			}
		} catch (SQLException e1) {
			// TODO 自動生成された catch ブロック
			e1.printStackTrace();
		}
		try {
			db.close();
		} catch (Exception e) {
			// TODO 自動生成された catch ブロック
			e.printStackTrace();
		}
		ServletContext sc = getServletContext();
		RequestDispatcher rd = sc.getRequestDispatcher("/test.jsp");
		request.setAttribute("count", record_count);
		request.setAttribute("employees", employees);
		rd.forward(request, response);
	}
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}
}